-- boolean=int转换函数 =========================================================================
--创建隐式类型转换方法
--其中booleanToSmallint(boolean) 建议是根据转换的类型进行创建 括号中的是需要转换的数据类型
-- smallint -> 可以更换为需要转换的数据类型
create or replace function booleanToSmallint(boolean) 
returns smallint as $$ 
begin 
  if $1 = true then 
    return 1;
  else 
    return 0;
  end if;
end;
$$ language plpgsql;

create cast(boolean as smallint) with function booleanToSmallint(boolean) as implicit;

-- find_in_set =========================================================================
CREATE OR REPLACE FUNCTION find_in_set(search_value text,string_list text)
RETURNS integer AS $BODY$
DECLARE position INTEGER;
BEGIN
	IF string_list = '' THEN 
		RETURN 0;
	ELSE
  		position := array_position(string_to_array(string_list, ','), search_value::TEXT);
  		RETURN position;
	END IF;
END;
$BODY$ language plpgsql;

-- if =========================================================================
create or replace function if(bln boolean,inValue1 anyelement,inValue2 anyelement)
returns anyelement as
$$
begin
if bln=true then
   return inValue1;
else
   return inValue2;
end if;
end;
$$
language plpgsql;

-- ifnull =========================================================================
CREATE OR REPLACE FUNCTION ifnull(anyelement, anyelement)
RETURNS anyelement AS $BODY$
BEGIN
  IF $1 IS NULL THEN
    RETURN $2;
  ELSE
    RETURN $1;
  END IF;
END;
$BODY$
LANGUAGE plpgsql;

-- null2Zero =========================================================================
-- 空值转为double 0
CREATE OR REPLACE FUNCTION null2zero(val double precision)
RETURNS double precision AS $$
BEGIN
  RETURN ifnull(val,0::double);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION null2zero(val text)
RETURNS double precision AS $$
BEGIN
  IF val ~ '^[+-]?([0-9]*[.])?[0-9]+$' THEN
    RETURN val::double precision;
  ELSE
    RETURN 0::double;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- round =========================================================================
-- 创建自定义的 ROUND 函数
CREATE OR REPLACE FUNCTION round(double precision, integer)
RETURNS double precision AS $$
DECLARE
    result double precision;
BEGIN
    result := ROUND($1::numeric, $2)::double precision;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- todouble  =========================================================================
CREATE OR REPLACE FUNCTION todouble(val text)
RETURNS double precision AS $$
BEGIN
  IF val ~ '^[+-]?([0-9]*[.])?[0-9]+$' THEN
    RETURN val::double precision;
  ELSE
    RETURN null;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION todouble(val text,defaultValue double precision)
RETURNS double precision AS $$
BEGIN
  IF val ~ '^[+-]?([0-9]*[.])?[0-9]+$' THEN
    RETURN val::double precision;
  ELSE
    RETURN defaultValue;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 空与非空   =========================================================================
create or replace function not_empty(str text)
returns boolean as
$$
begin
return str is not null and str!='';
end;
$$
language plpgsql;

create or replace function is_empty(str text)
returns boolean as
$$
begin
return str is null or str='';
end;
$$
language plpgsql;

-- 雪花ID生成   =========================================================================
--序列
CREATE SEQUENCE table_id_seq increment by 1 maxvalue 999999999999999 minvalue 1 start 1 cycle;

--函数
create or replace function snow_next_id(OUT result bigint) 
returns bigint as
$$
DECLARE
   our_epoch bigint := 1483200000000;
   seq_id bigint;
   now_millis bigint;
   shard_id int := 5;
BEGIN
   seq_id := nextval('table_id_seq') % 1024;
   SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
   result := (now_millis - our_epoch) << 23;
   result := result | (shard_id << 10);
   result := result | (seq_id);
END;
$$
language plpgsql;

--查询id
--generate_series(1,10)获取生成id数(1,10)为生成10个连续id
-- SELECT generate_series(1,10),SNOW_NEXT_ID() AS ID

-- 增速与占比   =========================================================================
-- 增速
CREATE OR REPLACE FUNCTION get_rate(val double precision,totalVal double precision,pointNum int)
RETURNS double precision AS $$
BEGIN
  IF totalVal is not null and totalVal!=0 THEN
    RETURN round(val/totalVal*100-100,pointNum);
  ELSE
    RETURN null;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 增速差
CREATE OR REPLACE FUNCTION get_ratediff(val1 double precision,totalVal1 double precision,val2 double precision,totalVal2 double precision,pointNum int)
RETURNS double precision AS $$
BEGIN
  RETURN round(ifnull(get_rate(val1,totalVal1,pointNum*2),0::double)-ifnull(get_rate(val2,totalVal2,pointNum*2),0::double),pointNum);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 占比
CREATE OR REPLACE FUNCTION get_percent(val double precision,totalVal double precision,pointNum int)
RETURNS double precision AS $$
BEGIN
  IF totalVal is not null and totalVal!=0 THEN
    RETURN round(val/totalVal*100,pointNum);
  ELSE
    RETURN null;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 直接除
CREATE OR REPLACE FUNCTION get_div(val double precision,totalVal double precision,pointNum int)
RETURNS double precision AS $$
BEGIN
  IF totalVal is not null and totalVal!=0 THEN
    RETURN round(val/totalVal,pointNum);
  ELSE
    RETURN null;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 允许sum字符串
CREATE OR REPLACE FUNCTION textsum_helper(a text, b text) RETURNS text AS $$
BEGIN
RETURN todouble(a,0::double precision)+todouble(b,0::double precision);
END;
$$ LANGUAGE plpgsql;

CREATE  OR REPLACE  AGGREGATE sum (text)
(
    sfunc = textsum_helper,
    stype = text,
    initcond = ''
);